Contents

Overview

The BIBLIO.MDB Database

Program Logic

Authors

Publishers

Titles

Title Author

Navigating the Database

Modifying the Database

Program Code

File List


The BIBLIO.MDB Database

Database Schema

Database Relations


BIBLIO: Overview
BIBLIO: An Overview

The BIBLIO sample application is a database browser designed to illustrate the use of the Data control.

It is implemented as a browser front end to the BIBLIO.MDB sample database of books about database programming.  The program uses six separate Data controls to present this information to the user in a manner that makes it easy to navigate through the titles, authors and publishers listed in the database.

To use the database browser, simply select Titles, Authors or Publishers by clicking on the appropriate command button, from the Select menu, or by clicking directly on the picture box itself.  You can move through each set of records by clicking on the Data control at the bottom of the form, or move instantly to related records by clicking on them in the list boxes.

See also:

Program Logic

Database Schema

Navigating the Database


Program Logic

The BIBLIO Database Browser is designed to allow easy browsing through a bibliography of database books.  To permit this, three separate forms are needed, one each for the Titles, Authors and Publishers tables.  These forms are implemented as Picture Box controls rather than MDI forms, to allow complete interaction among all the Data controls on all forms.

Each form is assigned a Data control with its Recordset property set to the corresponding table of the database.  In addition, each form contains a list box which will contain related information from other tables, and is controlled by a separate Data control.  Thus, there are six Data controls in the application.

When each of the main data controls is clicked (repositioned to a new record) the text box controls on its form are automatically updated with the values of the fields from the new record.  In addition, the Reposition event triggers a function to fill the associated list boxes.  For example, when the Publishers Data control is clicked, the following code executes:

Sub ListPubsTitles()

    Dim Qry As String

    'Build the query

    Qry = "SELECT DISTINCTROW Titles.Title FROM Titles"

    Qry = Qry + " WHERE (Titles.PubID=" + txtPubs(0).Text + ")"

    'Refresh the data control

    dcPubsTitles.RecordSource = Qry

    dcPubsTitles.Refresh

    'Clear the listbox and build the new list

    lstPubsTitles.Clear

    If dcPubsTitles.Recordset.EOF Then

        lstPubsTitles.AddItem "No titles for this publisher."

    End If

    Do While Not dcPubsTitles.Recordset.EOF

        lstPubsTitles.AddItem dcPubsTitles.Recordset(0)

        dcPubsTitles.Recordset.MoveNext

    Loop

End Sub

This fills the list box with the Titles published by that particular Publisher.  To move to the Titles record for one of these listed titles, clicking on the title in the list box executes the following code:

Private Sub lstPubsTitles_Click()

    Dim BM As Variant

    BM = dbCtrl(Titles).Recordset.Bookmark

    dbCtrl(Titles).Recordset.FindFirst _

        "Title = """ + lstPubsTitles.List(lstPubsTitles.ListIndex) + """"

    If dbCtrl(Titles).Recordset.NoMatch = True Then

        dbCtrl(Titles).Recordset.Bookmark = BM

    Else

        DisplayGroup_Click (Titles)

    End If

End Sub

This repositions the Titles Data control to the selected title, and brings the Titles form to the top of the stack.

The other forms and their related list boxes follow the same logic.

See also:

Overview

Database Schema


Database Schema

The schema, or database design, of the BIBLIO.MDB database consists of four related tables, as shown below:

Titles:

Title	Book title

Year Published	Publication date

ISBN	Unique key

PubID	Foreign key into Publishers table

Description	Reference info

Notes	General notes

Subject	Keywords

Comments	Description of book contents

Authors:

Au_ID	Unique key identifier

Author	Authors name

Year born	Authors birthdate

Publishers:

PubID	Unique key identifier

Name	Short (bibliographic) name

Company name	Full business name of publisher

Address	Publishers address

City	Publishers city

State	Publishers state

Zip	Zip code

Telephone	Phone number

Fax	Fax number

Comments	General comments on publisher

Title Author:

ISBN	Foreign key into Titles table

Au_ID	Foreign key into Authors table

NOTE:  The BIBLIO.MDB database was modified after the Data Access Guide for Visual Basic, version 4.0 went to print.  Where there are differences, the schema presented in this Help file is more up to date.

See also:

Authors

Publishers

Titles

Title Author


Relations in the BIBLIO Database

The BIBLIO.MDB schema creates several relations.  The most important ones are:

One to Many:

Publishers to Titles	A publisher can publish many titles

Many to Many:

Authors to Titles	An author can write many titles AND a title

can have many authors (collaborators).

Relations are implemented in the sample application through list boxes that display the Many side of the relation.  For example

•	The Titles form contains a list of the Authors of each title

•	The Authors form contains a list of Titles by each author

•	The Publishers form contains a list of the Titles release by each publisher


Authors

The Authors table lists basic information about each author.  Because it is in a Many-to-Many relation with the Titles table, it does not contain any foreign keys.  Instead, its connection to Titles is maintained through the Title Author table.

Authors:

Au_ID	Unique key identifier

Author	Authors name

Year born	Authors birthdate

The Authors form in the application lists therse fields.  It also contains a list box that is filled with the titles written by each author.  When the Authors data control is repositioned, the following query is executed and assigned to the Recordset property of the data control that fills the list box:

Qry = "SELECT Titles.Title FROM Titles INNER JOIN [Title Author] ON Titles.ISBN = [Title Author].ISBN WHERE ([Title Author].Au_ID=txtAuthors(1).Text)

where txtAuthors(1) is the text box that displays the Au_ID field.

See also:

Database Schema


Publishers

The Publishers table contains basic information about the Publisher of each volume in the bibliography database:

Publishers:

PubID	Unique key identifier

Name	Short (bibliographic) name

Company name	Full business name of publisher

Address	Publishers address

City	Publishers city

State	Publishers state

Zip	Zip code

Telephone	Phone number

Fax	Fax number

Comments	General comments on publisher

The Publishers form in the application lists these fields.  It also contains a list box that is filled with the titles published by each publisher.  When the Publishers data control is repositioned, the following query is executed and assigned to the Recordset property of the data control that fills the list box:

Qry = "SELECT DISTINCTROW Titles.Title FROM Titles WHERE (Titles.PubID=txtPubs(0).Text)

where txtPubs(0) is the text box that displays the PubID field.

See also:

Database Schema


Titles

The Titles table lists each book title in the bibliography

Titles:

Title	Book title

Year Published	Publication date

ISBN	Unique key

PubID	Foreign key into Publishers table

Description	Reference info

Notes	General notes

Subject	Keywords

Comments	Description of book contents

The Titles form in the application lists these fields.  It also contains a list box that is filled with the Author(s) written by each author.  When the Titles data control is repositioned, the following query is executed and assigned to the Recordset property of the data control that fills the list box:

Qry = "SELECT Authors.Author FROM Authors INNER JOIN [Title Author] ON Authors.Au_ID = [Title Author].Au_ID WHERE ([Title Author].ISBN=txtTitles(2).Text)

where txtTitles(2) is the text box that displays the ISBN field.

See also:

Database Schema


Title Author

The Title Author table is a two-field table whose only purpose is to define a many-to-many relation between the Titles table and the Authors table.

Title Author:

ISBN	Foreign key into Titles table

Au_ID	Foreign key into Authors table

Because this table works behind the scenes to define this relation, there is no screen for it in the applications user interface.  Instead, it is used to fill the list boxes that show the Author(s) of a particular Title, and the Titles written by a particular Author.

See also:

Database Schema

Relations


Navigating the Database

Navigating through records in the BIBLIO.MDB database is entirely visual:

1.	Select the form you want to start with by clicking on Titles, Authors or Publishers.

2.	Click the forward and backward arrows on the Data control to move from record to record.

3.	To move to a related record (for example, to move to a particular Title in the list box of titles shown on the Authors form), click on the item you want to move to.  The appropriate record will be found and its form placed on top of the stack.

See also:

Relations in the BIBLIO Database


Modifying the Database
Modifying the BIBLIO.MDB Database

Because of the many-to-many relations displayed on the various forms of the BIBLIO.MAK sample application, the database information is presented as Read-Only.  It is left to the user as an exercise to determine how to update a record which depends on a many-to-many relationship with another record or records displayed on the same form.

If you want to modify or add records to the BIBLIO.MDB database, you can do so by opening the database using Microsoft Access or the Data Manager application provided with Visual Basic version 4.0.

See also:

Database Schema

Program Logic


Code Listing
Code Listing for the BIBLIO Database Browser

BIBLIO.BAS:

'Declaration for WinHelp

Declare Function OSWinHelp% Lib "User" Alias "WinHelp" (ByVal hwnd%, ByVal HelpFile$, ByVal wCommand%, dwData As Any)

BIBLIO.FRM:

'Constant declarations for data control & picture box arrays

Const Titles% = 0

Const Authors% = 1

Const Publishers% = 2

'List all titles from a particular publisher

Sub ListPubsTitles()

    Dim Qry As String

    'Build the query

    Qry = "SELECT DISTINCTROW Titles.Title FROM Titles"

    Qry = Qry + " WHERE (Titles.PubID=" + txtPubs(0).Text + ")"

    'Refresh the data control

    dcPubsTitles.RecordSource = Qry

    dcPubsTitles.Refresh

    'Clear the listbox and build the new list

    lstPubsTitles.Clear

    If dcPubsTitles.Recordset.EOF Then

        lstPubsTitles.AddItem "No titles for this publisher."

    End If

    Do While Not dcPubsTitles.Recordset.EOF

        lstPubsTitles.AddItem dcPubsTitles.Recordset(0)

        dcPubsTitles.Recordset.MoveNext

    Loop

End Sub

'List all titles by a particular author

Sub ListAuthorTitles()

    Dim Qry As String

    'Build the query

    Qry = "SELECT Titles.Title FROM Titles"

    Qry = Qry + " INNER JOIN [Title Author] ON"

    Qry = Qry + " Titles.ISBN = [Title Author].ISBN WHERE"

    Qry = Qry + " (([Title Author].Au_ID=" + txtAuthors(1).Text + "))"

    'Refresh the data control

    dcAuthorTitles.RecordSource = Qry

    dcAuthorTitles.Refresh

    'Clear the listbox and build the new list

    lstAuthorTitles.Clear

    Do While Not dcAuthorTitles.Recordset.EOF

        lstAuthorTitles.AddItem dcAuthorTitles.Recordset(0)

        dcAuthorTitles.Recordset.MoveNext

    Loop

End Sub

'List the authors of a particular title

Sub ListTitleAuthors()

    Dim Qry As String

    'Build the query

    Qry = "SELECT Authors.Author FROM Authors"

    Qry = Qry + " INNER JOIN [Title Author] ON"

    Qry = Qry + " Authors.Au_ID = [Title Author].Au_ID WHERE"

    Qry = Qry + " (([Title Author].ISBN='" + txtTitles(2).Text + "'))"

    'Refresh the data control

    dcTitleAuthors.RecordSource = Qry

    dcTitleAuthors.Refresh

    'Clear the listbox and build the new list

    lstTitleAuthors.Clear

    Do While Not dcTitleAuthors.Recordset.EOF

        lstTitleAuthors.AddItem dcTitleAuthors.Recordset(0)

        dcTitleAuthors.Recordset.MoveNext

    Loop

End Sub

'Bring the selected picture box to the top by clicking button

Private Sub btnGroup_Click(Index As Integer)

    DisplayGroup(Index).ZOrder 0

End Sub

'Build the lists of related records when the current record changes

Private Sub dbCtrl_Reposition(Index As Integer)

    If Index = Publishers Then

        ListPubsTitles

    ElseIf Index = Authors Then

        ListAuthorTitles

    ElseIf Index = Titles Then

        ListTitleAuthors

    End If

End Sub

'Bring the selected picture box to the top by clicking picture box

Private Sub DisplayGroup_Click(Index As Integer)

    DisplayGroup(Index).ZOrder 0

End Sub

'Move to the selected Publisher when clicked in the Titles form

Private Sub lblTitlePub_Click()

    Dim BM As Variant, FindStr As String

    FindStr = "PubID = " + txtTitles(6).Text

    Debug.Print FindStr

    BM = dbCtrl(Publishers).Recordset.Bookmark

    dbCtrl(Publishers).Recordset.FindFirst FindStr

     If dbCtrl(Publishers).Recordset.NoMatch = True Then

        dbCtrl(Publishers).Recordset.Bookmark = BM

    Else

        DisplayGroup_Click (Publishers)

    End If

End Sub

'Move to the selected title when clicked in the Author's form

Private Sub lstAuthorTitles_Click()

    Dim BM As Variant, FindStr As String

    FindStr = "Title = """ + lstAuthorTitles.List(lstAuthorTitles.ListIndex) + """"

    BM = dbCtrl(Titles).Recordset.Bookmark

    dbCtrl(Titles).Recordset.FindFirst FindStr

    If dbCtrl(Titles).Recordset.NoMatch = True Then

        dbCtrl(Titles).Recordset.Bookmark = BM

    Else

        DisplayGroup_Click (Titles)

    End If

End Sub

'Move to the selected title when clicked in the Publisher's form

Private Sub lstPubsTitles_Click()

    Dim BM As Variant

    BM = dbCtrl(Titles).Recordset.Bookmark

    dbCtrl(Titles).Recordset.FindFirst _

        "Title = """ + lstPubsTitles.List(lstPubsTitles.ListIndex) + """"

    If dbCtrl(Titles).Recordset.NoMatch = True Then

        dbCtrl(Titles).Recordset.Bookmark = BM

    Else

        DisplayGroup_Click (Titles)

    End If

End Sub

'Move to the selected Author when clicked in the Titles form

Private Sub lstTitleAuthors_Click()

    Dim BM As Variant

    BM = dbCtrl(Authors).Recordset.Bookmark

    dbCtrl(Authors).Recordset.FindFirst _

        "Author = """ + lstTitleAuthors.List(lstTitleAuthors.ListIndex) + """"

    If dbCtrl(Authors).Recordset.NoMatch = True Then

        dbCtrl(Authors).Recordset.Bookmark = BM

    Else

        DisplayGroup_Click (Authors)

    End If

End Sub

'Bring the Author's picture box to the top

Private Sub mnuAuthors_Click()

    DisplayGroup_Click (Authors)

End Sub

'Bring the Publishers picture box to the top

Private Sub mnuPublishers_Click()

    DisplayGroup_Click (Publishers)

End Sub

'Bring the Titles picture box to the top

Private Sub mnuTitles_Click()

    DisplayGroup_Click (Titles)

End Sub

'End the program

Private Sub mnuExit_Click()

    End

End Sub

'Display the About box

Private Sub mnuHelpAbout_Click()

    frmAbout.Show 1

End Sub

'Open Help and display the Contents screen

Private Sub mnuHelpContents_Click()

  Dim Ret As Integer

  Ret = OSWinHelp(Me.hwnd, App.HelpFile, vbHelpContents, 0)

End Sub

'Open Help and display the Search window

Private Sub mnuHelpSearch_Click()

  Dim Ret As Integer

  Ret = OSWinHelp(Me.hwnd, App.HelpFile, vbHelpPartialKey, 0)

End Sub

See also:

Program Logic


File Listing
File List

The following files are used in the BIBLIO.MAK project:

PROJECT FILES:

BIBLIO.MAK	Project file

BIBLIO.FRM	Main form

BIBLIO.BAS	Global declarations

HELP FILE:

BIBLIO.HLP	Compiled Help file

DATABASE FILE:

BIBLIO.MDB	Bibliography of database programming books